"""Changed DeliveryZone > Shop to m2m, added delivery_zone_id to Stock, added working_delivery_zone_id to User and delivery_zone_id to Stock 

Revision ID: b1998be26387
Revises: 9292b11ae9e6
Create Date: 2024-03-26 13:07:23.765076

"""
import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision = "b1998be26387"
down_revision = "9292b11ae9e6"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "delivery_zones_shops",
        sa.Column("delivery_zone_id", sa.Integer(), nullable=True),
        sa.Column("shop_id", sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(
            ["delivery_zone_id"],
            ["delivery_zone.id"],
            name=op.f("fk_delivery_zones_shops_delivery_zone_id_delivery_zone"),
        ),
        sa.ForeignKeyConstraint(["shop_id"], ["shop.id"], name=op.f("fk_delivery_zones_shops_shop_id_shop")),
    )
    op.add_column("order", sa.Column("delivery_zone_id", sa.Integer(), nullable=True))
    op.create_foreign_key(
        op.f("fk_order_delivery_zone_id_delivery_zone"), "order", "delivery_zone", ["delivery_zone_id"], ["id"]
    )
    op.drop_constraint("fk_shop_delivery_zone_id_delivery_zone", "shop", type_="foreignkey")
    op.add_column("stock", sa.Column("delivery_zone_id", sa.Integer(), nullable=True))
    op.create_foreign_key(
        op.f("fk_stock_delivery_zone_id_delivery_zone"), "stock", "delivery_zone", ["delivery_zone_id"], ["id"]
    )
    op.add_column("user", sa.Column("working_delivery_zone_id", sa.Integer(), nullable=True))
    op.create_foreign_key(
        op.f("fk_user_working_delivery_zone_id_delivery_zone"),
        "user",
        "delivery_zone",
        ["working_delivery_zone_id"],
        ["id"],
    )

    # Move shop.delivery_zone_id to delivery_zones_shops table
    op.execute(
        """
            INSERT INTO delivery_zones_shops (delivery_zone_id, shop_id)
            SELECT delivery_zone_id, id 
            FROM shop
            WHERE delivery_zone_id IS NOT NULL
        """
    )
    op.drop_column("shop", "delivery_zone_id")

    # Set stock.delivery_zone_id to the ID of the first delivery zone from the delivery_zone table
    op.execute(
        """
            UPDATE stock 
            SET delivery_zone_id = (
                SELECT id FROM delivery_zone ORDER BY id LIMIT 1
            ) 
            WHERE delivery_zone_id IS NULL
        """
    )

    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(op.f("fk_user_working_delivery_zone_id_delivery_zone"), "user", type_="foreignkey")
    op.drop_column("user", "working_delivery_zone_id")
    op.drop_constraint(op.f("fk_stock_delivery_zone_id_delivery_zone"), "stock", type_="foreignkey")
    op.drop_column("stock", "delivery_zone_id")
    op.add_column("shop", sa.Column("delivery_zone_id", sa.INTEGER(), autoincrement=False, nullable=True))
    op.create_foreign_key(
        "fk_shop_delivery_zone_id_delivery_zone", "shop", "delivery_zone", ["delivery_zone_id"], ["id"]
    )
    op.drop_constraint(op.f("fk_order_delivery_zone_id_delivery_zone"), "order", type_="foreignkey")
    op.drop_column("order", "delivery_zone_id")

    # Move delivery_zones_shops.delivery_zone_id to corresponding shop.delivery_zone_id
    op.execute(
        """
            UPDATE shop 
            SET delivery_zone_id = d.delivery_zone_id
            FROM delivery_zones_shops d
            WHERE shop.id = d.shop_id
        """
    )
    op.drop_table("delivery_zones_shops")
    # ### end Alembic commands ###
